CREATE LANGUAGE plpgsql;

CREATE FUNCTION func_sincronized_estudiante() RETURNS trigger AS $sincronize_estudiante$
DECLARE
	aux_row estudiante_synchronized%ROWTYPE;
BEGIN
	IF (TG_OP = 'DELETE') THEN
		SELECT * INTO aux_row FROM estudiante_synchronized WHERE carnet = OLD.carnet;
		IF NOT FOUND THEN
			INSERT INTO estudiante_synchronized SELECT OLD.carnet, 'D',0;
		ELSE
            		UPDATE estudiante_synchronized SET operation = 'D' where carnet = aux_row.carnet;
		END IF;
        ELSIF (TG_OP = 'UPDATE') THEN
		SELECT * INTO aux_row FROM estudiante_synchronized WHERE carnet = OLD.carnet;
		IF NOT FOUND THEN
			INSERT INTO estudiante_synchronized SELECT OLD.carnet, 'U',0;
		ELSE
            		UPDATE estudiante_synchronized SET operation = 'U' where carnet = aux_row.carnet;
		END IF;
        ELSIF (TG_OP = 'INSERT') THEN
		SELECT * INTO aux_row FROM estudiante_synchronized WHERE carnet = NEW.carnet;
		IF NOT FOUND THEN
			INSERT INTO estudiante_synchronized SELECT NEW.carnet, 'I',0;
		ELSE
            		UPDATE estudiante_synchronized SET operation = 'I' where carnet = aux_row.carnet;
		END IF;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$sincronize_estudiante$ LANGUAGE plpgsql;				

CREATE TRIGGER sincronize_estudiante
AFTER INSERT OR UPDATE OR DELETE ON estudiante
    FOR EACH ROW EXECUTE PROCEDURE func_sincronized_estudiante();

